--------------------------------------------------------
--  File created - Wednesday-April-24-2013   
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Package Body ISBD_GENERATOR
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE BODY "ISBD"."ISBD_GENERATOR" AS

  procedure gen(amount in number) AS
    idr region.id%type;
    idc region.id%type;
    idd department.id%type;
    ide employee.id%type;
    bossidv department.bossid%type;
  BEGIN
    for rc in 1 .. amountr * amount loop
      select region_seq.nextval into idr from dual;
      
      insert 
      into region 
      (region.id, region.parentid, region.name)
      values (idr, null, gen_name(5));
      
      for cc in 1 .. amountc loop
        select region_seq.nextval into idc from dual;
      
        insert 
        into region 
        (region.id, region.parentid, region.name)
        values (idc, idr, gen_name(6));
        
        for dc in 1 .. amountd loop
          select department_seq.nextval into idd from dual;
        
          insert 
          into department 
          (department.id, department.parentid, department.regionid, department.bossid, department.name)
          values (idd, null, idc, null, gen_name(5));  
          
          for ec in 1 .. amounte loop
            select employee_seq.nextval into ide from dual;
        
            insert 
            into employee 
            (employee.id, employee.departmentid, employee.lastname, employee.firstname, employee.middlename)
            values (ide, idd, gen_name(6), gen_name(6), gen_name(6));  
            
            if (ec = 1) then 
              bossidv := ide;
            end if;  
          end loop;
          
          update department 
          set department.bossid = bossidv
          where department.id = idd;
        end loop;
      end loop;
    end loop;
  END gen;

  --not using cascade delete constraints:
  ---becsause of boss-lock
  ---it is not user safe allowing to purge all db in several clicks
  procedure wipe(idsremain in varchar2) AS
  BEGIN
    --id table region
    for idtr in 
    (
      select region.id 
      from region 
      where id not in 
      (
        SELECT REGEXP_SUBSTR(idsremain, '[^,]+', 1, level)
        FROM dual
        CONNECT BY INSTR(idsremain, ',', 1, level - 1) > 0
      )
      start with region.parentid is null
      connect by prior region.id = region.parentid
      order by level desc
    )
    loop
    --every region not in remain list
      for idtd in
      (
        select department.id, department.bossid
        from department
        where department.regionid = idtr.id
        start with department.parentid is null
        connect by prior department.id = department.parentid
        order by level desc
      )
      loop
      --every department of current region
        --will fire boss
        if( idtd.bossid is not null ) then
          update department set bossid = null where department.id = idtd.id;
        end if;
        
        --and any employee from
        delete from employee where employee.departmentid = idtd.id;
        --empty dep will be deleted then
        delete from department where department.id = idtd.id;
      end loop;
      
      --and current region do so
      delete from region where region.id = idtr.id;
    end loop;
    
    NULL;
  END wipe;

  function gen_name(len in number) return varchar2 AS
    namev varchar(4000);
  BEGIN
    --call string(varchar, integer) causes wrong behav!
    --normal string(varchar, number) 
    namev := dbms_random.string('U', len);
    namev := initcap(namev);
    return namev;
  END gen_name;
  
END ISBD_GENERATOR;

/

